#!/usr/bin/env python

import os
import time
import psycopg2
import sys
import pprint
import decimal
import smtplib
import getopt
import csv
import re
from datetime import date
from datetime import timedelta
import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders

MLPROD_HOME = os.environ.get("MLPROD_HOME")
sys.path.append(MLPROD_HOME + "/packages/python")
from xlwt import *

DBHOST = os.environ.get("PGHOST", "hdb-srv1")
DBNAME = os.environ.get("USER") + "_db"
DBUSER = "payorintel_" + os.environ.get("USER") + "_u"
DBPASSWD = ""
B_DATE = "" 
DIR = "/smbm/srv1/shared/Services/PayorIntel/Management_report_folder/" 
FILENAME = "" 
ERROREXIT = 1

def shellquote(s):
	return "'" + s.replace("'", "'\\''") + "'"

def getDBPASSWD():

	global DBPASSWD
	pwdfile = MLPROD_HOME + '/config/.mlprivate.cfg'
	p = os.popen("grep payorintel %s" % (shellquote(pwdfile)))
	pwdline = p.readline()
	p.close()
	if len(pwdline.split(' ')) > 1:		
		pwd = pwdline.strip().split(' ')[1]
	DBPASSWD = pwd

def getoptions():

	options, remainder = getopt.getopt(sys.argv[1:], 'b:c:', ['monthly'])
	dates = ''
	monthlyarg = ''
	custid = ''
	if len(options) > 2  or len(options) < 1:
		usage()
		print >>sys.stderr, "There can only be one option, either use batch date or monthly, cannot be both"
		sys.exit(ERROREXIT)
		#sys.exit("There can only be one option, either use batch date or monthly, cannot be both")
	else:
		for opt, arg in options:
			if opt in ('-b'):
				dates = arg
				if len(dates.split('_')) != 3:
					print >>sys.stderr, "batch date format error. correct format: MMDDYY_HHMMSS_CUSTID"
					sys.exit(ERROREXIT)
				else:
					custid = dates.split('_')[2]
			elif opt in ('-c'):
				custid = arg
				p = re.compile('^\d{1,6}$')
				if not p.match(custid):
					print >>sys.stderr, "cust id has to be a 1-6 digits number"
					sys.exit(ERROREXIT)
			else:
				if len(remainder) > 0:
					monthlyarg = remainder[0]
				else:
					usage()
					print >>sys.stderr, "please specify the month"
					sys.exit(ERROREXIT)
		if monthlyarg != '':
			if custid == '':
				usage()
				print >>sys.stderr, "with monthly option, cust id must be provided"
				sys.exit(ERROREXIT)
	return dates, monthlyarg, custid

def usage():
	print   """
USAGE:  python sum_by_hid.py -b MMDDYY_HHMMSS_CUSTID
	OR
	python sum_by_hid.py -c CUSTID --monthly YYYYMM
		"""
def datecaculation():
	today = date.today()
	offset1 = (today.weekday() + 1) % 7  + 7
	offset2 = (today.weekday() - 5) % 7
	start_day = today - timedelta(days=offset1)
	end_day = today - timedelta(days=offset2)
	return start_day, end_day

def generate_queries():

	arg, monthlyarg, custid = getoptions()
	global B_DATE
	global DIR 
	if arg != '':
		B_DATE = arg
        	args = arg.split('_')
        	batch_date = args[0]
		batch_time = args[1]
		if (len(batch_date) != 6 or len(batch_time) != 6):
			print >>sys.stderr, "Wrong format for batch date. Correct format: MMDDYY_HHMMSS_CUSTID"
			sys.exit(ERROREXIT)
		month = batch_date[:2]
		day = batch_date[2:4]
		year = str(int(batch_date[4:])+2000)
		hour = batch_time[:2]
		min = batch_time[2:4]
		sec = batch_time[4:]
        	cust_id = args[2] 
		create_query = """
			CREATE TEMP TABLE hid_sum AS
                        SELECT hid, count(case when medlytix_ins_type='M' then 1 end) as care_count,
                                Round(sum(case when medlytix_ins_type='M'  then charges end)) as care_sum,
                                count(case when medlytix_ins_type='D' then 1 end) as aid_count,
                                Round(sum(case when medlytix_ins_type='D' then charges end)) as aid_sum,
                                count(case when medlytix_ins_type='C' then 1 end) as comm_count,
                                Round(sum(case when medlytix_ins_type='C' then charges end)) as comm_sum,
                                count(*) as total_count,
                                Round(sum(charges)) as total_charges
                        FROM (SELECT DISTINCT ON(hid,acctnum) hid, charges, medlytix_ins_type 
                                FROM insurance2_results_t
                                WHERE   active_flag = 1
					AND suppress_flag = 0
					AND extract(month from our_run_date)=%s
                                        AND extract(day from our_run_date)=%s
                                        AND extract(year from our_run_date)=%s
					AND extract(hour from our_run_date)=%s
					AND extract(minute from our_run_date)=%s
					AND extract(second from our_run_date)=%s
                                        AND cust_id=%s 
                                        ) as ins
                                GROUP BY hid;			
		""" % (month, day, year, hour, min, sec, cust_id)
		query1 = """
			SELECT * from hid_sum;
			"""
		query2 = """
			 SELECT sum(care_count) as m_count, sum(care_sum) as m_sum, 
				sum(aid_count) as d_count, sum(aid_sum) as d_sum, 
				sum(comm_count) as c_count, sum(comm_sum) as c_sum, 
				sum(total_count) as total_count, sum(total_charges) as total_sum 
			FROM hid_sum;
			"""
	else:
		B_DATE = monthlyarg + "_" + custid 
		year = monthlyarg[:4]
		month = monthlyarg[4:]
		create_query = """
			CREATE TEMP TABLE hid_sum AS
                        SELECT hid, count(case when medlytix_ins_type='M' then 1 end) as care_count,
                                Round(sum(case when medlytix_ins_type='M'  then charges end)) as care_sum,
                                count(case when medlytix_ins_type='D' then 1 end) as aid_count,
                                Round(sum(case when medlytix_ins_type='D' then charges end)) as aid_sum,
                                count(case when medlytix_ins_type='C' then 1 end) as comm_count,
                                Round(sum(case when medlytix_ins_type='C' then charges end)) as comm_sum,
                                count(*) as total_count,
                                Round(sum(charges)) as total_charges
                        FROM (SELECT DISTINCT ON(hid,acctnum) hid, charges, medlytix_ins_type 
                                FROM insurance2_results_t
                                WHERE   active_flag = 1
                                        AND suppress_flag = 0
                                        AND extract(month from our_run_date)=%s
                                        AND extract(year from our_run_date)=%s
                                        AND cust_id=%s 
                                        ) as ins
                                GROUP BY hid;	
			""" % (month, year, custid)
		query1 = """
                         SELECT * from hid_sum;
                         """
                query2 = """
                         SELECT sum(care_count) as m_count, sum(care_sum) as m_sum, 
                                sum(aid_count) as d_count, sum(aid_sum) as d_sum, 
                                sum(comm_count) as c_count, sum(comm_sum) as c_sum, 
                                sum(total_count) as total_count, sum(total_charges) as total_sum 
                         FROM hid_sum;
                         """
	DIR = DIR + year + month
	return create_query, query1, query2

def getRcpts(cust_id):
	return  	"""
			SELECT email_management from insurance0_management_output_t
			WHERE cust_id=%s;
			""" % (cust_id)
			

def dbconnection():

        #start of script
        #Define our connection string
	getDBPASSWD()
        conn_string = "host=%s dbname=%s user=%s password=%s" % (DBHOST,DBNAME,DBUSER,DBPASSWD)
	batch_date, month, custid = getoptions()

        try:
                # get a connection, if a connect cannot be made an exception will be raised here
                conn = psycopg2.connect(conn_string)
                # conn.cursor will return a cursor object, you can use this cursor to perform queries
                cur = conn.cursor()
		create_query, query1, query2 = generate_queries()
                cur.execute(create_query)
		cur.execute(query1)
                records = cur.fetchall()
		cur.execute(query2)
		records2 = cur.fetchall()
		cur.execute(getRcpts(custid))
		rcpts = cur.fetchall()
        except:
                # Get the most recent exception
                exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                # Exit the script and print an error telling what happened.
                print >>sys.stderr, "Database connection failed!\n ->%s" % (exceptionValue)
		sys.exit(ERROREXIT)
	
	if len(records) > 0:
        	if not os.path.exists(DIR):
                	os.makedirs(DIR)
		global FILENAME
		TEMPDIR = MLPROD_HOME + "/tmp/" 
		FILENAME = "payorintel_summary_" + B_DATE + ".xls"
		FILELOCATION = DIR + "/" + FILENAME
		writexlwt(records, records2, TEMPDIR + FILENAME)
                if os.path.exists(FILELOCATION):
                        os.system("rm -f %s" % (FILELOCATION))
                os.system("cp -f %s %s" % (TEMPDIR + FILENAME, FILELOCATION))
                if os.path.exists(FILELOCATION):
                        os.system("rm %s" % (TEMPDIR + FILENAME)) 
		if len(rcpts) > 0:
			sendMail(
        			rcpts[0],
        			"Payorintel summary spreadsheet was generated","Payorintel summary spreadsheet by hid as attached",
        			FILELOCATION
    				)
	else:
		print >>sys.stderr, "There is no record generated with your criteria"
		sys.exit(ERROREXIT)

def writetofile(records, records2):
	MLPROD_HOME = os.environ.get("MLPROD_HOME")
	dir = MLPROD_HOME + '/archive/recon/out/'
	if not os.path.exists(dir):
    		os.makedirs(dir)
	FILENAME = 'hid_summary_' + B_DATE + ".csv"
	file = open(dir+FILENAME, 'wb')
	file.write(' , , Medicare, , Medicaid, , Commericial, , Total\n')
	file.write(' , hid, #, $, #, $, #, $, #, $\n')
	d = csv.writer(file, delimiter=',', quotechar='|')

	for r in records:
		x = list(r)
		y = x.insert(0, ' ')
		d.writerow(tuple(x))

	for rr in records2:
		a = list(rr)
		b = a.insert(0, 'Total')
		d.writerow(tuple(a))

	file.close()

def writexlwt(records, records2, FILENAME):
	
	workbook = Workbook()
	sheet = workbook.add_sheet('sheet 1')
	sheet.show_grid = 0

	#cell size formatting
        for i in range(len(records[0]) + 1):
                sheet.col(i).width = 3600
	
	LOGO = MLPROD_HOME + '/cust/payorintel/scripts/common/logo.bmp'
	sheet.insert_bitmap(LOGO, 1, 0)
	d = B_DATE.split('_')[0] 
	if len(B_DATE.split('_')) == 3:
		report_date = "Report for " + d[:2] + "/" + d[2:4]+ "/" + str(int(d[4:]) + 2000)
	else:
		report_date = "Report for " + d[4:] + "/" + d[:4]
	sheet.write(5,0, report_date, easyxf('font: italic on'))
	topHeader = ['Medicare', 'Medicaid', 'Commercial', 'Total']
	startrow = 6 
	sheet, row = write_topheader(topHeader, sheet, startrow)

	secondHeader = [ 'hid', '#', '$', '#', '$', '#', '$', '#', '$']
	sheet, row = write_secondheader(secondHeader, sheet, row)

	
	row = row + 1 
	sheet, row = write_body(records, sheet, row)	
	sheet, row = write_bottom(records2, sheet, row)

	workbook.save(FILENAME)

def write_topheader(topHeader, sheet, row):
	
        col = 1
	s_style = easyxf('border: top thin, right thin; align: wrap off, vert bottom, horiz center; font:bold on; pattern: fore-colour white')
	r_style = easyxf('border: top thin, right thin')
        for a in topHeader:
                sheet.write_merge(row, row, col, col + 1, a, s_style)
                col = col + 2
	return sheet, row

def write_secondheader(secondHeader, sheet, row):

        ezxf = easyxf
        h_style = ezxf('border: bottom thin; align: wrap off, vert bottom, horiz center; font:bold on; pattern: fore-colour white')
	r_style = ezxf('border: bottom thin, right thin; align: wrap off, vert bottom, horiz center; font:bold on; pattern: fore-colour white')
        m_style = ezxf('border: bottom thin, right thin, top thin; align: wrap off, vert center, horiz center; font:bold on; pattern: fore-colour white')
	col = 0
	sheet.write_merge(row, row + 1, col, col, secondHeader[0], m_style)
	col = col + 1
        for i in range(1, len(secondHeader)):
		if i % 2 == 0:
			sheet.write(row + 1, col, secondHeader[i],r_style)
		else:
                	sheet.write(row + 1, col, secondHeader[i], h_style)
                col = col + 1
	return sheet, row
		
def write_body(records, sheet, row):
	
	ezxf = easyxf
	kinds = 'str int money int money int money int money'.split(' ')
        kind_map = {
                'str': ezxf('border: right thin; align: wrap off, vert bottom, horiz center; pattern: pattern solid, fore-colour white'),
                'int': ezxf('pattern: pattern solid, fore-colour white', num_format_str='#,##0'),
                'money': ezxf('border: right thin; font: italic off; pattern: pattern solid, fore-colour white',
            num_format_str= '"$"#,##0_);("$"#,##'),
        }
        kind_map_alt = {
                'str': ezxf('border: right thin; align: wrap off, vert bottom, horiz center; pattern: pattern solid, fore-colour light_green'),
                'int': ezxf('font:bold off;pattern: pattern solid, fore-colour light_green', num_format_str='#,##0'),
                'money': ezxf('border: right thin; font:bold off;pattern: pattern solid, fore-colour light_green',
            num_format_str= '"$"#,##0_);("$"#,##'),

        }

        kind_map_bottom = {
                'str': ezxf('border: bottom thin; font:bold on; pattern: pattern solid, fore-colour olive_ega;border: top thick'),
                'int': ezxf('border: bottom thin; font:bold on; pattern: pattern solid, fore-colour olive_ega;border: top thick', num_format_str='#,##0'),
                'money': ezxf('border: right thin, bottom thin; font:bold on; pattern: pattern solid, fore-colour olive_ega;border: top thick',
            num_format_str= '"$"#,##0_);("$"#,##'),
        }
	ezr =  [kind_map[k] for k in kinds]
        ezr_alt = [kind_map_alt[k] for k in kinds]
	rowx = row

	for row in records:
                rowx += 1
                for colx, value in enumerate(row):
                        if rowx % 2 == 0:
                                sheet.write(rowx, colx, value, ezr[colx])
                        else:
                                sheet.write(rowx, colx, value, ezr_alt[colx])

	return sheet, rowx

def write_bottom(records2, sheet, row):
        ezxf = easyxf
	kinds_b = 'int money int money int money int money'.split(' ')

        kind_map_bottom = {
                'str': ezxf('border: bottom thin; font:bold on, colour white; pattern: pattern solid, fore-colour olive_ega;border: top thick'),
                'int': ezxf('border: bottom thin; font:bold on, colour white; pattern: pattern solid, fore-colour olive_ega;border: top thick', num_format_str='#,##0'),
                'money': ezxf('border: right thin, bottom thin; font:bold on, colour white; pattern: pattern solid, fore-colour olive_ega;border: top thick',
            num_format_str= '"$"#,##0_);("$"#,##'),
        }

        rowx = row
        ezr_b = [kind_map_bottom[k] for k in kinds_b]
	total_style = ezxf('border: right thin, bottom thin, top thick; font:bold on, colour white; pattern:pattern solid, fore-colour olive_ega; alignment:horiz center')
        col = 1
        sheet.write(rowx + 1, 0, 'Total', total_style)
        for row in records2:
                rowx += 1
                for colx, value in enumerate(row):
                        sheet.write(rowx, colx+1, value, ezr_b[colx])

	return sheet, rowx


def sendMail(to, subject, text, file,server="mailhost"):
	#assert type(to)==list
	fro = "mlprod\@medlytix.local"
	msg = MIMEMultipart()
	msg['From'] = fro
	msg['To'] = COMMASPACE.join(to)
	msg['Date'] = formatdate(localtime=True)
	msg['Subject'] = subject

	msg.attach( MIMEText(text) )

	part = MIMEBase('application', "octet-stream")
        part.set_payload( open(file,"rb").read() )
        Encoders.encode_base64(part)
        part.add_header('Content-Disposition', 'attachment; filename="%s"'
                      % os.path.basename(file))
        msg.attach(part)

	smtp = smtplib.SMTP(server)
	smtp.sendmail(fro, to, msg.as_string() )
	smtp.close()

def getStyle(bold, horz, vert, pattern):
	align =  Alignment()
	align.horz = getattr(Alignment, 'HORZ_%s' % horz)
	align.vert = getattr(Alignment, 'VERT_%s' % vert)
	myFont = Font()
	myFontStyle = XFStyle()
	if bold:
		myFont.bold = True
	if pattern:
		myPattern = Pattern()
		myPattern.pattern_fore_colour = 0x16 
		myFontStyle.pattern = myPattern
	myFontStyle.font = myFont
	myFontStyle.alignment = align

	return myFontStyle
	

if __name__ == '__main__':
	dbconnection()
